package etl.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class TestOracle {
	private String sConnStr = "";
	private String sDBDriver = "oracle.jdbc.OracleDriver";
	/**
	* 缺省构造器
	*/
	public TestOracle() {
//		sConnStr = "jdbc:oracle:thin:@HAWK:1521:orcl";
		sConnStr = "jdbc:oracle:thin:@192.168.161.234:1521:ORCL123";
	}
	/**
	* @param ip,serviceName
	*/
	public TestOracle(String ip,String serviceName) {
		sConnStr = "jdbc:oracle:thin:@"+ip+":1521:"+serviceName;
	}
	/**
	* 通过thin方式获得Oracle数据库的连接.
	*/
	public Connection connectDbByThin() {
		Connection conn=null;
		try {
			Class.forName(sDBDriver);
			conn = DriverManager.getConnection(sConnStr,"drtest","res23db");
		} catch (Exception e){
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void main(String[] args) throws SQLException {
		TestOracle toi = new TestOracle();
		Connection conn = toi.connectDbByThin();
	
		conn.setAutoCommit(false);
		
		Statement stmt = null;
		
		ResultSet rs = null;
		
		stmt = conn.createStatement();
		
		String sql = "select * from prm2.test where 1=2 ";
		rs = stmt.executeQuery(sql);
		java.sql.ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		for(int i=0; i<columnCount; i++){
			String columnName = rsmd.getColumnName(i+1);
			String columnClassName = rsmd.getColumnClassName(i+1);
			String columnDbType = rsmd.getColumnTypeName(i+1);
			int columnSQLType = rsmd.getColumnType(i+1);
			
			System.out.println("columnName: "+columnName+", columnDbType: "+columnDbType+", columnSQLType: "+columnSQLType+", columnClassName:"+columnClassName);
		}
//		String userName = "pra";
//		List<String> dbinfo = new ArrayList<String>();
//		StringBuffer sql = new StringBuffer("select eds.data_source_name,eds.data_source_dbtype,eds.data_soure_ip,eds.data_soure_port,eds.data_source_dbname,eds.data_source_user from "+userName+".tblbiz_rc_table rt,");
//		sql.append(userName).append(".emcd_solution_resource esr,").append(userName).append(".emcd_solution_table est,").append(userName).append(".emcd_monitor_tbale emt,").append(userName).append(".emcd_monitor_model emm,");
//		sql.append(userName).append(".emcd_data_source eds WHERE rt.secid = esr.rtid AND esr.stid = est.ID AND est.emc_id = emt.ID AND emt.modelid = emm.ID AND emm.datasourceid = eds.data_source_id AND rt.tablename_cn='");
//		sql.append("MYSQL").append("'");
//		
//		rs = stmt.executeQuery(sql.toString());
//		
//		rs.next();
//		
//		dbinfo.add(rs.getString(1));
//		dbinfo.add(rs.getString(2));
//		dbinfo.add(rs.getString(3));
//		dbinfo.add(rs.getString(4));
//		dbinfo.add(rs.getString(5));
//		dbinfo.add(rs.getString(6));
//		
//		for (int i = 0; i < dbinfo.size(); i++) {
//			System.out.println(dbinfo.get(i));
//		}
		/*
		StringBuffer sBuffer = new StringBuffer("");
		
		String[] str = {"801","61","900","999"};
		
		for(int k=0;k<str.length;k++){
			sBuffer.append(
				"SELECT A.RCTYPE,"+
					   "A.DEPTNAME,"+
				       "A.RCNAME,"+
				       "A.TABLENAME_CN,"+
				       "A.SECID,"+
				       "B.CQFS5,"+
				       "B.ZQ6,"+
				       "B.SJY7 "+
				  "FROM (SELECT A.DBSID,"+
				               "B.OWNERNAME || '.' || B.TABLENAME AS TABLENAME,"+
				               "B.SECID AS PARENTID,"+
				               "B.TABLENAME_CN,"+
				               "B.SECID,"+
				               "A.RCNAME,"+
				               "A.DEPTNAME,"+
				               "B.RCTYPE "+
				          "FROM TBLBIZ_RC A, TBLBIZ_RC_TABLE B "+
				         "WHERE A.SECID = B.RCID) A,"+
				       "(SELECT A.DATASRCID,"+
				               "(SELECT JDBC_THIN_LINK "+
				                  "FROM EMCD_DATA_SOURCE "+
				                 "WHERE DATA_SOURCE_ID = A.DATASRCID) AS SJY7,"+
				               "A.SRCUSER || '.' || B.TABLENAME AS TABLENAME,"+
				               "B.EMC_ID,"+
				               "(SELECT (SELECT NAME "+
				                          "FROM EMCD_MONTYPE_DIC "+
				                         "WHERE CODE = A.MONITORTYPE) "+
				                  "FROM EMCD_MONITOR_TBALE A "+
				                 "WHERE ID = B.EMC_ID) as CQFS5,"+
				               "B.ID AS DSTTABLEID,"+
				               "(SELECT (SELECT NAME FROM EMCD_TYPE_DIC WHERE CODE = A.TYPE) "+
				                  "FROM EMCD_SCHEDULER A "+
				                 "WHERE A.PARENTID = B.ID) AS ZQ6 "+
				          "FROM EMCD_SOLUTION A, EMCD_SOLUTION_TABLE B "+
				         "WHERE A.ID = B.PARENTID) B "+
				 "WHERE B.DATASRCID = A.DBSID "+
				   "AND A.TABLENAME = B.TABLENAME AND A.SECID='"+Long.parseLong(str[k])+"'");
			System.out.println(sBuffer.toString());
			ResultSet rset = stmt.executeQuery(sBuffer.toString());
			sBuffer.delete(0, sBuffer.length());
			while (rset.next()) {
				System.out.println (rset.getString(1)+" "+rset.getString(2)+" "+rset.getString(3)+" "+rset.getString(4)+" "+rset.getString(5)+" "+" "+rset.getString(6)+" "+rset.getString(7)+" "+rset.getString(8));
			}
			rset.close();
		}
		//System.out.println(rset.getMetaData().getColumnName(4));

		*/
		
		rs.close();
		stmt.close();
		conn.close();
		System.out.println ("Ok.");
	}

	
}
